﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Easy.Common;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web.Configuration;
using Easy.DAL;

namespace Easy.Operation
{
    public class EntitySetting_Function
    {
        public static List<EntityMap> GetEntityList()
        {

            DataSet ds = null;

            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetEntity";
            ds = sqlhelper.ExecuteDataSet(strProcedureName, new object[] { DBNull.Value });
            List<EntityMap> list = new List<EntityMap>();


            foreach (DataRow item in ds.Tables[0].Rows)
            {
                EntityMap entity = new EntityMap();
                entity.EntityMapId = item["EntityMapId"].ToString();
                entity.EntityName = item["EntityName"].ToString();
                entity.EntityDisplayName = item["EntityDisplayName"].ToString();
                entity.Desc = item["Desc"].ToString();
                entity.CreatedOn = item["createdon"].ToString();
                entity.type = item["type"].ToString();

                list.Add(entity);
            }
            return list;


        }

        public static EntityMap GetEntity(string EntityId)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetEntity";

            ds = sqlhelper.ExecuteDataSet(strProcedureName, new Guid(EntityId));
            List<EntityMap> list = new List<EntityMap>();


            foreach (DataRow item in ds.Tables[0].Rows)
            {
                EntityMap entity = new EntityMap();
                entity.EntityMapId = item["EntityMapId"].ToString();
                entity.EntityName = item["EntityName"].ToString();
                entity.EntityDisplayName = item["EntityDisplayName"].ToString();
                entity.Desc = item["Desc"].ToString();
                entity.CreatedOn = item["createdon"].ToString();
                entity.type = item["type"].ToString();

                list.Add(entity);
            }
            return list.Count > 0 ? list[0] : null;


        }

        public static string AddEntity(AddEntityClass add)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);
            string strProcedureName = "sp_CreateEntity";

            object[] param = new object[] {
               add.EntityName,
               add.EntityDisplayName,
               add.Desc,
               new Guid(ConfigurationManager.AppSettings["AdminUserId"].ToString()),
               Guid.NewGuid()
            };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);

            return ds.Tables[0].Rows[0]["EntityId"].ToString();

        }

        public static void UpdateEntity(UpdateEntityClass update)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_UpdateEntity";

            object[] param = new object[] {
               new Guid(update.EntityId),
               update.EntityDisplayName,
               update.Desc,
               new Guid(ConfigurationManager.AppSettings["AdminUserId"].ToString())
            };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);


        }


        public static List<AttributeMap> GetAttributeList(string EntityId)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetAttribute";
            object[] param = { 
                                new Guid(EntityId),
                                DBNull.Value
                             };


            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);
            List<AttributeMap> list = new List<AttributeMap>();


            foreach (DataRow item in ds.Tables[0].Rows)
            {
                AttributeMap entity = new AttributeMap();
                entity.AttributeMapId = item["AttributeMapId"].ToString();
                entity.name = item["name"].ToString();
                entity.DisplayName = item["DisplayName"].ToString();

                entity.CreatedOn = item["createdon"].ToString();
                entity.type = item["type"].ToString();
                entity.length = item["length"].ToString();
                entity.IsNullable = item["IsNullable"].ToString();
                entity.Cansearchable = item["Cansearchable"].ToString();
                entity.picklistvalue = item["picklistvalue"].ToString();
                entity.IsSystemAtt = item["IsSystemAtt"].ToString();

                list.Add(entity);
            }
            return list;


        }

        //modifyby:kyzhao
        public static List<AttributeMap> GetAttributeListNew(string EntityId, string procedureName)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);
            List<AttributeMap> list = new List<AttributeMap>();


            string strProcedureName = procedureName;
            object[] param = { 
                             "1",
                             DBNull.Value,
                             "",
                             DBNull.Value,
                             "",

                             "",
                             "",
                            new Guid(EntityId),
                            DBNull.Value,
                            "",

                            "",
                            "",
                            "",
                            ""
                         };


            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);

            foreach (DataRow item in ds.Tables[0].Rows)
            {
                AttributeMap entity = new AttributeMap();
                entity.AttributeMapId = item["AttributeMapId"].ToString();
                entity.name = item["Name"].ToString();
                entity.DisplayName = item["DisplayName"].ToString();

                entity.CreatedOn = item["createdon"].ToString();
                entity.type = item["Type"].ToString();
                entity.length = item["Length"].ToString();
                entity.IsNullable = item["IsNullable"].ToString();
                entity.Cansearchable = item["Cansearchable"].ToString();
                entity.picklistvalue = item["picklistvalue"].ToString();
                entity.IsSystemAtt = item["IsSystemAtt"].ToString();
                entity.EntityName = item["EntityName"].ToString();
                entity.LinkEntityName = item["LinkEntityName"].ToString();
                entity.LinkAttributeName = item["LinkAttributeName"].ToString();

                list.Add(entity);
            }

            return list;


        }



        public static AttributeMap GetAttribute(string AttributeId)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetAttribute";

            object[] param = { DBNull.Value, new Guid(AttributeId) };

            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);
            List<AttributeMap> list = new List<AttributeMap>();


            foreach (DataRow item in ds.Tables[0].Rows)
            {
                AttributeMap entity = new AttributeMap();
                entity.AttributeMapId = item["AttributeMapId"].ToString();
                entity.name = item["name"].ToString();
                entity.DisplayName = item["DisplayName"].ToString();

                entity.CreatedOn = item["createdon"].ToString();
                entity.type = item["type"].ToString();
                entity.length = item["length"].ToString();
                entity.IsNullable = item["IsNullableValue"].ToString();
                entity.Cansearchable = item["CansearchableValue"].ToString();
                entity.picklistvalue = item["picklistvalue"].ToString();
                entity.lookupentity = item["lookupentity"].ToString();
                entity.IsGlobalPicklist = item["IsGlobalPicklist"].ToString();
                entity.GlobalPicklist = item["GlobalPicklist"].ToString();
                entity.IsSystemAtt = item["IsSystemAtt"].ToString();

                list.Add(entity);
            }
            return list.Count > 0 ? list[0] : null;

        }

        public static void AddAttribute(AddAttributeClass add)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_CreateAttribute";

            object[] param = new object[] {
              add.EntityId,
              add.name,
              add.DisplayName,
              add.type,
              add.length,
              add.Cansearchable=="1",
              add.IsNullable=="1",
               ConfigurationManager.AppSettings["AdminUserId"].ToString(),
               add.picklistvalue,
               add.lookupentity,
               add.IsGlobalPicklist=="1",
               add.GlobalPicklist,
               Guid.NewGuid()
            };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);



        }

        public static void UpdateAttribute(UpdateAttributeClass update)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_UpdateAttribute";

            object[] param = new object[] {
              update.AttributeId,
              update.DisplayName,
              update.Cansearchable=="1",
              update.IsNullable=="1",
               ConfigurationManager.AppSettings["AdminUserId"].ToString(),
               update.picklistvalue
            };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);
        }

        public static void SetPrefix(string prefix)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_SetPrefix";

            ds = sqlhelper.ExecuteDataSet(strProcedureName, new string[] { prefix });



        }
        public static string GetPrefix()
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetPrefix";

            ds = sqlhelper.ExecuteDataSet(strProcedureName, new object[] { });
            return ds.Tables[0].Rows[0][0].ToString();


        }

        public static List<GlobalPicklistClass> GetGlobalPicklist()
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetGlobalOptionList";

            ds = sqlhelper.ExecuteDataSet(strProcedureName, new object[] { DBNull.Value });
            List<GlobalPicklistClass> list = new List<GlobalPicklistClass>();


            foreach (DataRow item in ds.Tables[0].Rows)
            {
                GlobalPicklistClass glo = new GlobalPicklistClass();
                glo.GlobalOptionId = item["GlobalOptionId"].ToString();
                glo.Name = item["Name"].ToString();
                glo.DisplayName = item["DisplayName"].ToString();


                list.Add(glo);
            }
            return list;
        }
        public static GlobalPicklistClass GetGlobalPicklist(string GlobalOptionId)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetGlobalOptionList";

            ds = sqlhelper.ExecuteDataSet(strProcedureName, new object[] { new Guid(GlobalOptionId) });
            List<GlobalPicklistClass> list = new List<GlobalPicklistClass>();


            foreach (DataRow item in ds.Tables[0].Rows)
            {
                GlobalPicklistClass glo = new GlobalPicklistClass();
                glo.GlobalOptionId = item["GlobalOptionId"].ToString();
                glo.Name = item["Name"].ToString();
                glo.DisplayName = item["DisplayName"].ToString();
                glo.picklistvalue = item["picklistvalue"].ToString();


                list.Add(glo);
            }
            return list.Count > 0 ? list[0] : null;
        }

        public static string AddGlobalStringMap(GlobalPicklistClass add)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_CreateGlobalStringMap";

            object[] param = new object[] {
               DBNull.Value,
               add.Name,
               add.DisplayName,
               add.picklistvalue,
               new Guid(ConfigurationManager.AppSettings["AdminUserId"].ToString()),
               Guid.NewGuid()
            };
            if (add.GlobalOptionId != "")
            {
                param[0] = new Guid(add.GlobalOptionId);
            }

            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);

            return ds.Tables[0].Rows[0]["Name"].ToString();
        }

        public static bool CheckAttributeName(string EntityId, string AttributeName)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_CheckAttributeName";

            object[] param = new object[] {
               new Guid(EntityId) ,
              AttributeName
            };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);

            return ds.Tables[0].Rows[0][0].ToString() == "0" ? true : false;
        }

        //4.19 DeleteEntity
        public static bool isDeleteEntity(string EntityName)
        {
            //DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);
            string strProcedureName = "sp_DeleteEntity";
            //ds = sqlhelper.ExecuteDataSet(strProcedureName,EntityName);
            int isdelete = sqlhelper.ExecuteNonQuery(strProcedureName, EntityName);
            return isdelete == 0 ? false : true;
        }

        //4.20 DeleteAttribute
        public static bool isDeleteAttribute(string AttributeName, string EntityName, string type)
        {
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);
            string strProcedureName = "sp_DeleteAttribute";
            object[] param = new object[]{
                AttributeName,
                EntityName,
                type
            };
            int isdelete = sqlhelper.ExecuteNonQuery(strProcedureName, param);
            return isdelete == 0 ? false : true;
        }
        //2016-6-2,delete form,zhuxj
        public static bool isDeleteFrom(string FormName, string EntityName)
        {
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);


            string sql = @"delete from [Form] where EntityName=@entityname and FormName = @formname";
            DbCommand cmd = sqlhelper.GetSqlStringCommand(sql);
            cmd.Parameters.Add(new SqlParameter("@Entityname", EntityName));
            cmd.Parameters.Add(new SqlParameter("@FormName", FormName));
            int isdelete = sqlhelper.ExecuteNonQuery(cmd);
            return isdelete == 0 ? false : true;
        }
        //zw,delete View
        public static bool isDeleteView(string ViewMapName, string EntityName)
        {

            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_View_Delete";

            string[] param = new string[] {
                                                ViewMapName,
                                                EntityName
            };
            int isdelete = sqlhelper.ExecuteNonQuery(strProcedureName, param);
            return isdelete == 0 ? false : true;
        }
        //zw,delete Relation
        public static bool isDeleteRelation(string RelationName)
        {

            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);
            string strProcedureName = "sp_DeleteRelation";
            int isdelete = sqlhelper.ExecuteNonQuery(strProcedureName, new string[] { RelationName });
            return isdelete == 0 ? false : true;
        }


        public static List<RelationMapClass> GetRelationList(string EntityId)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetRelationList";

            ds = sqlhelper.ExecuteDataSet(strProcedureName, new Guid(EntityId));
            List<RelationMapClass> list = new List<RelationMapClass>();


            foreach (DataRow item in ds.Tables[0].Rows)
            {
                RelationMapClass rmc = new RelationMapClass();
                rmc.RelationMapId = item["RelationMapId"].ToString();
                rmc.RelationEntity = item["RelationEntity"].ToString();
                rmc.EntityDisplayName = item["EntityDisplayName"].ToString();
                rmc.RelationName = item["RelationName"].ToString();

                list.Add(rmc);
            }
            return list;
        }

        public static string AddRelation(AddRelationClass add)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_CreateRelation";

            object[] param = new object[] {
               add.EntityName1,
               add.EntityName2,
               add.RelationName,
              new Guid(ConfigurationManager.AppSettings["AdminUserId"].ToString())
            };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);

            return ds.Tables[0].Rows[0]["RelationMapId"].ToString();
        }

        public static string AddViewMap(ViewMapClass view)
        {
            EntityMap entity = GetEntity(view.EntityId);
            //if(entity.EntityName.ToLower()!="tlm_formdemo")
            //{
            //    //先删除所有这个视图的字段
            //    DataSet ds = null;
            //    SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);
            //    string strProcedureName = "sp_View_Save";

            //    object[] param1 = new object[] {
            //       view.Name,
            //       view.DisplayName,
            //       entity.EntityName,
            //       DBNull.Value,//"@AttributeName",
            //       DBNull.Value,//"@Order" int
            //       DBNull.Value,//"@Width",
            //       new Guid(Config.AdminUserId),
            //       "delete",
            //       view.ViewFilter,
            //       DBNull.Value,//"@ViewOrderBy"
            //       view.ViewAdditionalJs
            //    };
            //    ds = sqlhelper.ExecuteDataSet(strProcedureName, param1);

            //    var attlist = view.viewAttvalue.Split('β');
            //    for (int i = 0; i < attlist.Length - 1; i++)
            //    {
            //        var arr = attlist[i].Split('α');
            //        object[] param2 = new object[] { 
            //       view.Name,   //ViewName
            //       DBNull.Value, //view.DisplayName
            //       entity.EntityName,//EntityName
            //       arr[0],//AttributeName
            //       arr[1],//Order
            //       arr[2],//Width
            //       new Guid(Config.AdminUserId),// @userid
            //       DBNull.Value,//@type
            //       DBNull.Value,//ViewFilter
            //       arr[3],//ViewOrderBy  
            //       DBNull.Value
            //    };
            //        ds = sqlhelper.ExecuteDataSet(strProcedureName, param2);
            //    }
            //    return view.Name;
            //}
            //else
            //{
            return AddViewMapNew(view);
            //}
        }

        //addby:kyzhao 2016-06-13 视图配置保存修改
        //modifyby:kyzhao 2016-07-22 增加视图类型
        public static string AddViewMapNew(ViewMapClass view)
        {
            EntityMap entity = GetEntity(view.EntityId);

            //先删除所有这个视图的字段
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);
            string strProcedureName = "sp_View_SaveNew";

            object[] param1 = new object[] {
                   view.Name,
                   view.DisplayName,
                   entity.EntityName,
                   DBNull.Value,//"@AttributeName",
                   DBNull.Value,//"@Order" int

                   DBNull.Value,//"@Width",
                   new Guid(Config.AdminUserId),
                   "delete",
                   view.ViewFilter,
                   DBNull.Value,//"@ViewOrderBy"

                   view.ViewAdditionalJs,
                   view.ViewIsDisplay=="0"?false:true,
                   DBNull.Value,
                   view.ViewClientType,
                   view.ViewAttProcedure,
                   view.ViewTemplete,
                   DBNull.Value,
                   DBNull.Value
                };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param1);

            var attlist = view.viewAttvalue.Split('β');
            for (int i = 0; i < attlist.Length - 1; i++)
            {
                var arr = attlist[i].Split('α');
                object[] param2 = new object[] { 
                   view.Name,   //ViewName
                   DBNull.Value, //view.DisplayName
                   entity.EntityName,//EntityName
                   arr[0],//AttributeName
                   arr[1],//Order

                   arr[2],//Width
                   new Guid(Config.AdminUserId),// @userid
                   DBNull.Value,//@type
                   DBNull.Value,//ViewFilter
                   arr[3],//ViewOrderBy
  
                   DBNull.Value,
                   DBNull.Value,
                   arr[4],
                   DBNull.Value,
                   DBNull.Value,
                   DBNull.Value,
                   arr[5],
                   arr[6]
                };
                ds = sqlhelper.ExecuteDataSet(strProcedureName, param2);
            }
            return view.Name;
        }
        //modify by :kyzhao 2016-06-13 tlm_formdemo视图使用最新配置
        public static ViewMapClass GetViewMapInfo(string EntityName, string ViewMapName)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);
            
            string strProcedureName = "sp_GetViewMapInfoNew";

            string[] param = new string[] {
                        EntityName,
                      ViewMapName
                };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);


            ViewMapClass vmc = new ViewMapClass();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                vmc.Name = item["ViewName"].ToString();
                vmc.DisplayName = item["ViewDisplayName"].ToString();
                vmc.viewAttvalue = item["viewAttvalue"].ToString();
                vmc.ViewFilter = item["ViewFilter"].ToString();
                //vmc.ViewAdditionalJs = item["ViewAdditionalJs"].ToString();
                vmc.ViewIsDisplay = item["ViewIsDisplay"].ToString();
                vmc.ViewAttProcedure = item["ViewAttProcedure"].ToString();
                vmc.ViewClientType = item["ViewClientType"].ToString();
                vmc.ViewTemplete = item["ViewTemplete"].ToString();
            }
            JsHubDAL dal = new JsHubDAL();
            vmc.ViewJsList = dal.GetJsListWithEntityItemName("view", EntityName, ViewMapName);
            return vmc;

        }

        public static List<ViewMapClass> GetViewMapList(string EntityName)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetViewMapInfoNew";

            object[] param = { 
                                EntityName,     //@EntityName nvarchar(200) = null,
                                DBNull.Value    //@ViewMapName nvarchar(200) = null
                             };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);


            List<ViewMapClass> list = new List<ViewMapClass>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                ViewMapClass vmc = new ViewMapClass();
                vmc.Name = item["ViewName"].ToString();
                vmc.DisplayName = item["ViewDisplayName"].ToString();

                list.Add(vmc);
            }
            return list;
        }

        public static FormClass GetFormInfo(string EntityName, string FormName)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetFormInfo";

            string[] param = new string[] {
                       FormName,
                       EntityName
            };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);


            FormClass form = new FormClass();
            foreach (DataRow item in ds.Tables[0].Rows)
            {

                form.FormName = item["FormName"].ToString();
                form.DisplayName = item["DisplayName"].ToString();
                form.EntityName = item["EntityName"].ToString();
                form.FormHtml = item["FormHtml"].ToString();
                try
                {
                    form.FormType = int.Parse(item["FormTypeFlag"].ToString());
                }
                catch (Exception)
                {

                    form.FormType = 0;
                }
            }
            return form;
        }

        public static List<FormClass> GetFormList(string EntityName)
        {
            DataSet ds = null;
            SqlDatabase sqlhelper = new SqlDatabase(Config.TulingBuyCrmConnectString);

            string strProcedureName = "sp_GetFormInfo";

            object[] param = { 
                                 DBNull.Value,       //@FormName nvarchar(200) = null,
                                 EntityName       //@EntityName nvarchar(200) = null
                             };
            ds = sqlhelper.ExecuteDataSet(strProcedureName, param);


            List<FormClass> list = new List<FormClass>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                FormClass form = new FormClass();
                form.FormName = item["FormName"].ToString();
                form.DisplayName = item["DisplayName"].ToString();
                try
                {
                    form.FormType = int.Parse(item["FormTypeFlag"].ToString());
                }
                catch (Exception)
                {

                    form.FormType = 0;
                }

                form.EntityName = item["EntityName"].ToString();
                list.Add(form);
            }
            return list;
        }
    }
}